﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public DataSet QueryVERSART(DataRow dwData, int option = 1)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            string TableName = "VERSART";
            OracleCommand or1;

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT* FROM VERSART WHERE VVERSART=:VVERSART AND VSPED=:VSPED";
                    or1.Parameters.Add("VVERSART", OracleType.NVarChar).Value = dwData["VVERSART"].ToString();
                    or1.Parameters.Add("VSPED", OracleType.NVarChar).Value = dwData["VSPED"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT DISTINCT S.* FROM VVLIEF V JOIN  VERSART  S ON VVERSART=VVVERSART AND VSPED=VVSPED JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVLIEFSCH"
               + " WHERE VVTMANDANT=:VVTMANDANT AND VVTAUFKRS = :VVTAUFKRS AND VVTAUFLFD=:VVTAUFLFD";
                    or1.Parameters.Add("VVTMANDANT", OracleType.NVarChar).Value = dwData["VVTMANDANT"].ToString();
                    or1.Parameters.Add("VVTAUFKRS", OracleType.NVarChar).Value = dwData["VVTAUFKRS"].ToString();
                    or1.Parameters.Add("VVTAUFLFD", OracleType.NVarChar).Value = dwData["VVTAUFLFD"].ToString();
                    break;
                case 3:
                    or1.CommandText = "SELECT DISTINCT VVLIEFSCH, S.* FROM VVLIEF V JOIN  VERSART  S ON VVERSART=VVVERSART AND VSPED=VVSPED JOIN VVTLIEF T ON V.VVMANDANT=VVTMANDANT AND VVLIEFSCH=VVLIEFSCH  WHERE  VVNEUDAT>='" + dwData["DSTART"].ToString() + "' AND VVNEUDAT<='" + dwData["DEND"].ToString() + "' AND VVSTATUS IN (" + dwData["C20"].ToString() + ")"; 
                    break;
                default:
                    break;
            }
            setResult = Connect2Proxy(or1);
            if (setResult.Tables.Count > 0)
            {
                setResult.Tables[0].TableName = TableName;
            }
            setReturn.Merge(setResult);

            return setReturn;
        }
    }
}
